﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UCL.Powersource.Analysis;
using Excel = Microsoft.Office.Interop.Excel;

namespace UCL.Powersource.Exports
{
    public class XLSExports
    {

        public static Excel.Worksheet GetNewTeamExport(ITfsTeamProjectConfiguration itfsTeamProject, string saveasfilename, Excel.Application app)
        {
            var lastWsh = XLSExports.GetNewWorkbookWithOneSheet("ExportInfo", saveasfilename, app);
            app = lastWsh.Application;
            lastWsh.Activate();
            var wb = app.ActiveWorkbook;
            Excel.Range rng;// = wb.Sheets[1].Cells;
            //var lastWsh = (Excel.Worksheet)wb.Worksheets[1];
            int maxInfoRow = 1;
            lastWsh.Cells[maxInfoRow, 1] = "Automatic Powersource Export";
            maxInfoRow++;
            lastWsh.Cells[maxInfoRow, 1] = "Team";
            lastWsh.Cells[maxInfoRow, 2] = itfsTeamProject.Label;
            maxInfoRow++;
            lastWsh.Cells[maxInfoRow , 1] = "Exported from";
            lastWsh.Cells[maxInfoRow , 2] = itfsTeamProject.Uri;
            maxInfoRow ++;
            lastWsh.Cells[maxInfoRow, 1] = "Time of export";
            lastWsh.Cells[maxInfoRow, 2] = DateTime.Now;
            

            //lastWsh.Cells[3, 2] = CurrentTfsTeamProjectCollectionConfiguration.Uri.;
            rng = (Excel.Range)lastWsh.Rows[1];
            rng.Font.Size = 14;
            rng.Font.Bold = true;
            rng = (Excel.Range)lastWsh.Rows[2];
            rng.Font.Size = 14;
            rng.Font.Bold = true;
            rng = (Excel.Range)lastWsh.Columns[1];
            rng.ColumnWidth = 15;
            rng.Font.Bold = true;
            rng = lastWsh.Range[lastWsh.Cells[1, 2], lastWsh.Cells[maxInfoRow, 2]];
            rng.Font.Italic = true;
            return lastWsh;
        }

        public static Excel.Worksheet GetNewProfessorExport(ITfsTeamProjectCollectionConfiguration tfsTeamProjectCollectionConfiguration, Func<IEnumerable<IStatistic>> statsSelector, Func<IEnumerable<ITimeFrame>> timeFramesSelector)
        {
            var lastWsh = XLSExports.GetNewWorkbookWithOneSheet("ExportInfo");

            var app = lastWsh.Application;
            lastWsh.Activate();
            var wb = app.ActiveWorkbook;
            Excel.Range rng;// = wb.Sheets[1].Cells;
            //var lastWsh = (Excel.Worksheet)wb.Worksheets[1];
            lastWsh.Cells[1, 1] = "Automatic Powersource Export";
            lastWsh.Cells[2, 1] = "Exported from";
            lastWsh.Cells[2, 2] = tfsTeamProjectCollectionConfiguration.Uri;
            lastWsh.Cells[3, 1] = "Time of export";
            lastWsh.Cells[3, 2] = DateTime.Now;

            //lastWsh.Cells[3, 2] = CurrentTfsTeamProjectCollectionConfiguration.Uri.;
            rng = (Excel.Range)lastWsh.Rows[1];
            rng.Font.Size = 16;
            rng.Font.Bold = true;
            rng = (Excel.Range)lastWsh.Columns[1];
            rng.ColumnWidth = 15;
            rng.Font.Bold = true;
            rng = (Excel.Range)lastWsh.Columns[2];
            rng.ColumnWidth = 40;
            rng.Font.Italic = true;
            return lastWsh;
        }


        public static Excel.Worksheet GetNewWorkbookWithOneSheet(string oneSheetWorkbook_WorksheetName, string saveasfilename = null, Excel.Application app = null)
        {
            if (app == null) app = new Excel.Application();
            app.Visible = true;
            var wb = (Excel.Workbook)app.Workbooks.Add();
            wb.Activate();
            //app.Windows[1].Activate();
            if (!string.IsNullOrWhiteSpace(saveasfilename))
            {
                saveasfilename = RemoveIllegalCharacters(saveasfilename);
                wb.SaveAs(Filename: saveasfilename);
            }
            while (wb.Worksheets.Count > 1)
            {
                wb.Worksheets[1].Delete();
            }

            if (!string.IsNullOrWhiteSpace(oneSheetWorkbook_WorksheetName))
            {

                wb.Worksheets[1].Name = oneSheetWorkbook_WorksheetName;
            }
            return wb.Worksheets[1];
        }


        public static void ExportDataSetToWorksheet(Excel.Workbook wbk, DataSet dt, Excel.Worksheet after = null)
        {
            Excel.Worksheet lastSht = after;
            if (lastSht == null) lastSht = wbk.Worksheets[wbk.Worksheets.Count];
            
            foreach (DataTable table in dt.Tables)
            {
                var sht = wbk.Worksheets.Add(After: lastSht);
                ExportDataTableToWorksheet(sht, table);
                lastSht = sht;
            }

        }

        public static void ExportDataTableToRange(Excel.Range rng, DataTable dt, bool withTableColumnHeaders = true)
        {
            if (withTableColumnHeaders)
            {
                rng.Cells[1, 1] = dt.TableName;
                rng = rng.Cells[2, 1];
            }

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                rng.Cells[1, i + 1] = dt.Columns[i].Caption;
            }
            //rng = rng.Cells[2, 1];
            var startRow = rng.Row;
            for (int row0Index = 0; row0Index < dt.Rows.Count; row0Index++)
            {
                var crow = dt.Rows[row0Index];

                rng = rng.Worksheet.Range[rng.Worksheet.Cells[startRow + row0Index + 1, 1], rng.Worksheet.Cells[startRow + row0Index + 1, dt.Columns.Count]];
                rng.Value = crow.ItemArray;
            }

        }

        public static void ExportDataTableToWorksheet(Excel.Worksheet sht, DataTable dt, bool withTableColumnHeaders = true)
        {
            ExportDataTableToRange(sht.Cells, dt, withTableColumnHeaders);
            sht.Name = dt.TableName;
        }

        public static string RemoveIllegalCharacters(string filename)
        {
            string invalid = new string(Path.GetInvalidFileNameChars()) + new string(Path.GetInvalidPathChars());
            var sb = new StringBuilder(filename);
            foreach (var it in invalid)
            {
                sb.Replace(it, '_');
            }
            return sb.ToString();
        }


        public static void Finish(Excel.Application app, Excel.Workbook wb, bool saveChanges, string xlsFileName = null)
        {
            if (string.IsNullOrWhiteSpace( xlsFileName)) return;
            if (saveChanges) wb.SaveAs(xlsFileName);
            if (wb!=null) wb.Close();
            wb = null;
            app.Visible = false;
            app.Quit();
            app = null;
        }

        public static void FailsafeFinish(ILog Log, Excel.Application app, Excel.Workbook wb, bool saveChanges)
        {
            try
            {
                if (wb != null) wb.Close(SaveChanges: saveChanges);
                wb = null;
            }
            catch (Exception iexc)
            {
                Log.Error(iexc, "Closing wb - exception thrown");
            }
            try
            {
                if (app != null) app.Quit();
                app = null;
            }
            catch (Exception iexc)
            {
                Log.Error(iexc, "Closing app - exception thrown");
            }
        }

        public static DataSet CreateTimeFrame_StatisticCol_TeamRowDataset(IEnumerable<IStatistic> allStats, IEnumerable<ITfsTeamProjectConfiguration> allTeams, IEnumerable<ITimeFrame> allTimeframes)
        {
            var ds = new DataSet();


            foreach (var tf in allTimeframes)
            {
                var tableName = new StringBuilder(tf.Label);
                var specCharacters = "\\//*.?!#";
                foreach (var ch in specCharacters)
                {
                    tableName.Replace(ch, '-');
                }

                DataTable dt = new DataTable(tableName.ToString());
                ds.Tables.Add(dt);

                dt.BeginLoadData();
                var dcTeam = new DataColumn();
                dcTeam.ColumnName = "TeamName";
                dcTeam.Caption = "Team name";
                dcTeam.DataType = typeof(string);
                dt.Columns.Add(dcTeam);

                foreach (var stat in allStats)
                {
                    var dc = new DataColumn();
                    dc.ColumnName = stat.Name;
                    dc.DataType = typeof(double);
                    dc.Caption = stat.Label;
                    dt.Columns.Add(dc);
                    dc.AllowDBNull = true;
                }
                foreach (var team in allTeams)
                {
                    DataRow rw = dt.NewRow();
                    dt.Rows.Add(rw);
                    rw[dcTeam] = team.Label;
                    foreach (var stat in allStats)
                    {
                        if (
                            stat.TeamResults.ContainsKey(team)
                            &&
                            stat.TeamResults[team].ContainsKey(tf)
                            &&
                            stat.TeamResults[team][tf] != null
                            &&
                            stat.TeamResults[team][tf].Value != null
                            )
                            rw[stat.Name] = stat.TeamResults[team][tf].Value;
                        else
                            rw[stat.Name] = DBNull.Value;
                    }
                }
                dt.EndLoadData();



                //var sht = (Excel.Worksheet)wb.Worksheets.Add(After: lastWsh);
                //var lastWsh = sht;
                //sht.Name = tableName.ToString();
            }

            return ds;
        }



        public static DataSet CreateTeam_TimeFrameCol_StatisticRowDataset(IEnumerable<IStatistic> stats, IEnumerable<ITfsTeamProjectConfiguration> allTeams, IEnumerable<ITimeFrame> allTFs)
        {
            var ds = new DataSet();


            foreach (var team in allTeams)
            {
                var tableName = GetTableName(team);

                var dt = new DataTable(tableName.ToString());
                ds.Tables.Add(dt);
                dt.BeginLoadData();
                var dcStat = new DataColumn();
                dcStat.ColumnName = "Statistics";
                dcStat.Caption = "Statistic";
                dcStat.DataType = typeof(string);
                dt.Columns.Add(dcStat);

                var tfIndex = 0;
                foreach (var tf in allTFs)
                {
                    var colName = (tf.SourceBranchPath ?? string.Empty) + tfIndex.ToString();

                    var dc = new DataColumn();
                    dc.ColumnName = colName;
                    dc.DataType = typeof(double);
                    dc.Caption = tf.Label;
                    dt.Columns.Add(dc);
                    dc.AllowDBNull = true;
                    tfIndex++;
                }
                tfIndex = 0;

                foreach (var stat in stats)
                {
                    DataRow rw = dt.NewRow();
                    dt.Rows.Add(rw);
                    tfIndex = 0;
                    rw[dcStat] = stat.Label;
                    foreach (var tf in allTFs)
                    {
                        var colName = (tf.SourceBranchPath ?? string.Empty) + tfIndex.ToString();
                        if (
                            stat.TeamResults.ContainsKey(team)
                            &&
                            stat.TeamResults[team].ContainsKey(tf)
                            &&
                            stat.TeamResults[team][tf] != null
                            &&
                            stat.TeamResults[team][tf].Value != null
                            )
                            rw[colName] = stat.TeamResults[team][tf].Value;
                        else
                            rw[colName] = DBNull.Value;
                        tfIndex++;
                    }
                    tfIndex = 0;
                }
                dt.EndLoadData();



                //var sht = (Excel.Worksheet)wb.Worksheets.Add(After: lastWsh);
                //var lastWsh = sht;
                //sht.Name = tableName.ToString();
            }

            return ds;
        }

        public static string GetTableName(ITfsTeamProjectConfiguration team)
        {
            var tableName = new StringBuilder(team.Label);
            var specCharacters = "\\//*.?!#";
            foreach (var ch in specCharacters)
            {
                tableName.Replace(ch, '-');
            }
            return tableName.ToString();
        }
    }
}
